﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Task_Network_Center.General;
using MySql.Data.MySqlClient;
using System.Windows;
using System.Data;

namespace Task_Network_Center.DataBaseTable
{
    class PrioriteTable
    {
        MySqlConnection conn = null;
        MySqlConnection conn2 = null;

        public object[,] ListPrioriteToStringArray()
        {
            object[,] result = new object[3, getPrioriteRowCount()];
            int i = 0;
            try
            {
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();
                conn.Open();
                String query = "SELECT * FROM tableconfigpriorite";
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    result[0, i] = dr.GetInt32(0);
                    result[1, i] = dr.GetString(1);
                    result[2, i] = i;
                    i++;
                }
                dr.Close();
                conn.Close();
                conn = null;

                return result;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur TablePriorite - ListPrioriteToStringArray: " + e);
                return null;
            }
        }


        private Int64 getPrioriteRowCount()
        {
            try
            {
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();

                String query = "SELECT COUNT(*) FROM tableconfigpriorite";
                MySqlCommand cmd = new MySqlCommand(query, conn);
                conn.Open();

                Int64 result = (Int64)cmd.ExecuteScalar();

                conn.Close();
                conn = null;

                return result;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur TablePriorite - getPrioriteRowCount: " + e);
                return -1;
            }
        }

        public DataTable QueryTableReadable(string query)
        {

            DataTable result = new DataTable();
            result.Columns.Add("id_config_priorite");
            result.Columns.Add("priorite");

            string id = "", priorite = "";


            try
            {
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();
                conn.Open();
                //String query = "SELECT * FROM tableconfigpriorite";
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    if (!dr.IsDBNull(0))
                    {
                        id = dr.GetInt32(0).ToString();
                    }
                    else
                    {
                        id = "";
                    }

                    if (!dr.IsDBNull(1))
                    {
                        priorite = dr.GetString(1);
                    }
                    else
                    {
                        priorite = "";
                    }



                    result.Rows.Add(new Object[] { id, priorite });
                }
                dr.Close();
                conn.Close();
                conn = null;

                return result;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur TablePriorite - QueryTableReadable: " + e);
                return null;
            }
        }


        public bool InsertRow(string priorite)
        {
            String query = "INSERT INTO tableconfigpriorite (id_config_priorite,priorite) VALUE (@p1,@p2)";

            int id = prioriteID();

            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddWithValue("p1", id);
                cmd.Parameters.AddWithValue("p2", priorite);

                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur PrioriteTable - InsertRow: " + e);
                return false;
            }

        }


        public bool UpdateRow(int id, string priorite)
        {
            String query = "UPDATE tableconfigpriorite SET priorite = @p1 WHERE id_config_priorite = @p2";
            conn = GCGGeneralLib.DataBaseConnection.DbConnection();
            MySqlCommand cmd = new MySqlCommand(query, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddWithValue("p2", id);
                cmd.Parameters.AddWithValue("p1", priorite);
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;

            }
            catch (Exception e)
            {

                MessageBox.Show("Erreur PrioriteTable - UpdateRow: " + e);
                return false;
            }

        }


        private int prioriteID()
        {

            conn2 = GCGGeneralLib.DataBaseConnection.DbConnection();
            String query = "SELECT LAST_INSERT_ID() FROM tableconfigpriorite";
            MySqlCommand cmd = conn2.CreateCommand();
            cmd.CommandText = query;
            try
            {

                conn2.Open();
                cmd.ExecuteScalar();
                int count = Convert.ToInt32(cmd.LastInsertedId);
                conn2.Close();
                conn2 = null;
                return count + 1;
            }
            catch (Exception e)
            {
                MessageBox.Show("Erreur PrioriteTable - prioriteID: " + e);
                return -1;
            }

        }

    }
}
